package database.customer;


import com.sun.istack.internal.NotNull;
import database.CRUD;

import javax.swing.table.DefaultTableModel;
import java.sql.*;

public class tb_customer {//客户表
    @NotNull
private String Cid;//客户编号
    @NotNull
private String CcompanyName;//客户名称
    @NotNull
private String CcompanySName;//客户简称
    @NotNull
private String CcompanyAddress;//地址
private String CcompanyPhone;//公司电话
private String Cemail;//邮件
    @NotNull
private String Cname;//联系人
    @NotNull
private String CtelPhone;//联系人电话
private String other;//备注
public tb_customer() {}
public tb_customer(String Cid, String CcompanyName, String CcompanySName, String CcompanyAddress, String CcompanyPhone, String Cemail, String Cname, String CtelPhone, String other) {
    this.Cid=Cid;
    this.CcompanyName=CcompanyName;
    this.CcompanySName=CcompanySName;
    this.CcompanyAddress=CcompanyAddress;
    this.CcompanyPhone=CcompanyPhone;
    this.Cemail=Cemail;
    this.Cname=Cname;
    this.CtelPhone=CtelPhone;
    this.other=other;
}

    public void setCid(String cid) {
        Cid = cid;
    }
    public void setCcompanyName(String ccompanyName) {
        CcompanyName = ccompanyName;
    }
    public void setCcompanySName(String ccompanySName) {
    CcompanySName = ccompanySName;
    }
    public void setCcompanyAddress(String ccompanyAddress) {
    CcompanyAddress = ccompanyAddress;
    }
    public void setCcompanyPhone(String ccompanyPhone) {
    CcompanyPhone = ccompanyPhone;
    }
    public void setCemail(String cemail) {
    Cemail = cemail;
    }
    public void setCname(String cname) {
    Cname = cname;
    }
    public void setCtelPhone(String ctelPhone) {
    CtelPhone = ctelPhone;
    }
    public void setOther(String other) {
    this.other = other;
    }
    public String getCid() {
    return Cid;
    }
    public String getCcompanyName() {
    return CcompanyName;
    }
    public String getCcompanySName() {
    return CcompanySName;
    }
    public String getCcompanyAddress() {
    return CcompanyAddress;
    }
    public String getCcompanyPhone() {
    return CcompanyPhone;
    }
    public String getCemail() {
    return Cemail;
    }
    public String getCname() {
    return Cname;
    }
    public String getCtelPhone() {
    return CtelPhone;
    }
    public String getOther() {
    return other;
    }

    // 插入新客户信息
    public boolean insertCustomer(String Cid, String CcompanyName, String CcompanySName, String CcompanyAddress,
                                  String CcompanyPhone, String Cemail, String Cname, String CtelPhone, String other) {
        String sql = "INSERT INTO tb_customer (Cid, CcompanyName, CcompanySName, CcompanyAddress, CcompanyPhone, Cemail, Cname, CtelPhone, other) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置预编译语句的参数值
            pstmt.setString(1, Cid);
            pstmt.setString(2, CcompanyName);
            pstmt.setString(3, CcompanySName);
            pstmt.setString(4, CcompanyAddress);
            pstmt.setString(5, CcompanyPhone);
            pstmt.setString(6, Cemail);
            pstmt.setString(7, Cname);
            pstmt.setString(8, CtelPhone);
            pstmt.setString(9, other);

            // 执行更新操作
            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                return true; // 插入成功
            } else {
                return false; // 插入失败
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 插入失败
        }
    }


    // 更新客户信息
    public boolean updateCustomer(String Cid,String CcompanyName,String CcompanySName,String CcompanyAddress,String CcompanyPhone,String Cemail,String Cname,String CtelPhone,String other) {
        String sql = "UPDATE tb_customer SET CcompanyName = ?, CcompanySName = ?, CcompanyAddress = ?, CcompanyPhone = ?, Cemail = ?, Cname = ?, CtelPhone = ?, other = ? "
                + "WHERE Cid = ?";
        Object[] params = {CcompanyName, CcompanySName, CcompanyAddress, CcompanyPhone, Cemail, Cname, CtelPhone, other,Cid};
        try {
            CRUD.executeUpdate(sql
            );
            return true; // 更新成功
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 更新失败
        }
    }

    // 删除客户信息
    public boolean deleteCustomer(String attribute, String value) {
        String sql;
        if ("Cid".equalsIgnoreCase(attribute)) {
            //如果是Eid的的话，直接查询
            sql = "DELETE FROM tb_customer WHERE Cid = ?";
        } else {
            // 如果是其他的需要添加属性查询
            sql = "SELECT * FROM tb_customer WHERE " + attribute + " = ?";
        }

        try {
            PreparedStatement pstmt = CRUD.getConnection().prepareStatement(sql);
            pstmt.setString(1, value);

            if ("Cid".equalsIgnoreCase(attribute)) {
                int rowsAffected = pstmt.executeUpdate();
                return rowsAffected > 0;
            } else {
                //sql查询出来的行
                ResultSet rs = pstmt.executeQuery();
                int rowCount = 0;
                while (rs.next()) {
                    rowCount++;
                    String eid = rs.getString("Cid");
                    String ename = rs.getString("Ccompanyname");
                    System.out.println("找到客户：Cid=" + eid + ", Ccompanyname=" + ename);
                }

                if (rowCount == 0) {
                    System.out.println("没有找到 " + attribute + " = " + value + " 的客户");
                    return false;
                } else if (rowCount == 1) {

                    int rowsAffected = pstmt.executeUpdate();
                    return rowsAffected > 0;
                } else {
                    System.out.println("找到多个符合条件的客户 " + attribute + " = " + value);
                    return false;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // Delete failed
        }
    }

    // 查询客户信息
    public DefaultTableModel queryCustomers() {
        String sql = "SELECT * FROM tb_customer";
        try {
            return CRUD.executeQueryAndBuildTableModel(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return null; // 查询失败
        }
    }
}
